"""
数据库存储模块
实现SQLite数据库存储和数据模型
"""

import json
import time
from datetime import datetime
from typing import List, Dict, Optional, Any
from sqlalchemy import create_engine, Column, Integer, String, Float, Text, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.exc import SQLAlchemyError

from ..core import config, storage_logger
from ..postprocessing import ProcessedText, TextSegment, SourceType

# 为了兼容性，使用Text类型存储JSON数据
try:
    from sqlalchemy import JSON
except ImportError:
    JSON = Text

Base = declarative_base()


class MeetingRecord(Base):
    """会议记录表"""
    __tablename__ = "meeting_records"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    meeting_id = Column(String(100), unique=True, nullable=False, index=True)
    title = Column(String(200), nullable=True)
    description = Column(Text, nullable=True)
    start_time = Column(DateTime, nullable=False)
    end_time = Column(DateTime, nullable=True)
    status = Column(String(20), default="active")  # active, completed, archived
    metadata = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


class TextRecord(Base):
    """文本记录表"""
    __tablename__ = "text_records"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    text_id = Column(String(100), unique=True, nullable=False, index=True)
    meeting_id = Column(String(100), nullable=False, index=True)
    content = Column(Text, nullable=False)
    cleaned_content = Column(Text, nullable=True)
    source_type = Column(String(20), nullable=False)  # slide, whiteboard, discussion
    confidence = Column(Float, nullable=False)
    timestamp = Column(DateTime, nullable=False)
    language = Column(String(10), nullable=True)
    keywords = Column(JSON, nullable=True)
    region_ids = Column(JSON, nullable=True)
    metadata = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)


class SegmentRecord(Base):
    """段落记录表"""
    __tablename__ = "segment_records"
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    segment_id = Column(String(100), unique=True, nullable=False, index=True)
    meeting_id = Column(String(100), nullable=False, index=True)
    content = Column(Text, nullable=False)
    source_type = Column(String(20), nullable=False)
    start_time = Column(DateTime, nullable=False)
    end_time = Column(DateTime, nullable=False)
    confidence = Column(Float, nullable=False)
    keywords = Column(JSON, nullable=True)
    related_segments = Column(JSON, nullable=True)
    metadata = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)


class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self):
        self.database_url = config.storage.database_url
        self.engine = None
        self.SessionLocal = None
        
        self._initialize_database()
        storage_logger.info("数据库管理器初始化完成")
    
    def _initialize_database(self):
        """初始化数据库"""
        try:
            # 创建数据库引擎
            self.engine = create_engine(
                self.database_url,
                echo=config.system.debug,
                pool_pre_ping=True,
                pool_recycle=3600
            )
            
            # 创建会话工厂
            self.SessionLocal = sessionmaker(
                autocommit=False,
                autoflush=False,
                bind=self.engine
            )
            
            # 创建表
            Base.metadata.create_all(bind=self.engine)
            
            storage_logger.info(f"数据库初始化成功: {self.database_url}")
            
        except Exception as e:
            storage_logger.error(f"数据库初始化失败: {e}")
            raise
    
    def get_session(self) -> Session:
        """获取数据库会话"""
        return self.SessionLocal()
    
    def create_meeting(self, meeting_id: str, title: Optional[str] = None,
                      description: Optional[str] = None) -> bool:
        """创建会议记录"""
        try:
            with self.get_session() as session:
                # 检查是否已存在
                existing = session.query(MeetingRecord).filter(
                    MeetingRecord.meeting_id == meeting_id
                ).first()
                
                if existing:
                    storage_logger.warning(f"会议记录已存在: {meeting_id}")
                    return False
                
                # 创建新记录
                meeting = MeetingRecord(
                    meeting_id=meeting_id,
                    title=title or f"Meeting {meeting_id}",
                    description=description,
                    start_time=datetime.utcnow(),
                    status="active"
                )
                
                session.add(meeting)
                session.commit()
                
                storage_logger.info(f"创建会议记录: {meeting_id}")
                return True
                
        except SQLAlchemyError as e:
            storage_logger.error(f"创建会议记录失败: {e}")
            return False
    
    def save_text_records(self, meeting_id: str, 
                         processed_texts: List[ProcessedText]) -> bool:
        """保存文本记录"""
        try:
            with self.get_session() as session:
                for text in processed_texts:
                    # 检查是否已存在
                    existing = session.query(TextRecord).filter(
                        TextRecord.text_id == text.text_id
                    ).first()
                    
                    if existing:
                        continue
                    
                    # 创建文本记录
                    record = TextRecord(
                        text_id=text.text_id,
                        meeting_id=meeting_id,
                        content=text.content,
                        cleaned_content=text.cleaned_content,
                        source_type=text.source_type.value,
                        confidence=text.confidence,
                        timestamp=datetime.fromtimestamp(text.timestamp),
                        language=text.language,
                        keywords=text.keywords,
                        region_ids=text.region_ids
                    )
                    
                    session.add(record)
                
                session.commit()
                storage_logger.info(f"保存 {len(processed_texts)} 条文本记录")
                return True
                
        except SQLAlchemyError as e:
            storage_logger.error(f"保存文本记录失败: {e}")
            return False
    
    def save_segment_records(self, meeting_id: str, 
                           segments: List[TextSegment]) -> bool:
        """保存段落记录"""
        try:
            with self.get_session() as session:
                for segment in segments:
                    # 检查是否已存在
                    existing = session.query(SegmentRecord).filter(
                        SegmentRecord.segment_id == segment.segment_id
                    ).first()
                    
                    if existing:
                        continue
                    
                    # 创建段落记录
                    record = SegmentRecord(
                        segment_id=segment.segment_id,
                        meeting_id=meeting_id,
                        content=segment.content,
                        source_type=segment.source_type.value,
                        start_time=datetime.fromtimestamp(segment.start_time),
                        end_time=datetime.fromtimestamp(segment.end_time),
                        confidence=segment.confidence,
                        keywords=segment.keywords,
                        related_segments=segment.related_segments
                    )
                    
                    session.add(record)
                
                session.commit()
                storage_logger.info(f"保存 {len(segments)} 条段落记录")
                return True
                
        except SQLAlchemyError as e:
            storage_logger.error(f"保存段落记录失败: {e}")
            return False
    
    def get_meeting_records(self, limit: int = 100, 
                           offset: int = 0) -> List[Dict[str, Any]]:
        """获取会议记录列表"""
        try:
            with self.get_session() as session:
                meetings = session.query(MeetingRecord)\
                    .order_by(MeetingRecord.start_time.desc())\
                    .limit(limit)\
                    .offset(offset)\
                    .all()
                
                return [self._meeting_to_dict(meeting) for meeting in meetings]
                
        except SQLAlchemyError as e:
            storage_logger.error(f"获取会议记录失败: {e}")
            return []
    
    def get_meeting_by_id(self, meeting_id: str) -> Optional[Dict[str, Any]]:
        """根据ID获取会议记录"""
        try:
            with self.get_session() as session:
                meeting = session.query(MeetingRecord).filter(
                    MeetingRecord.meeting_id == meeting_id
                ).first()
                
                if meeting:
                    return self._meeting_to_dict(meeting)
                return None
                
        except SQLAlchemyError as e:
            storage_logger.error(f"获取会议记录失败: {e}")
            return None
    
    def get_text_records(self, meeting_id: str) -> List[Dict[str, Any]]:
        """获取会议的文本记录"""
        try:
            with self.get_session() as session:
                texts = session.query(TextRecord).filter(
                    TextRecord.meeting_id == meeting_id
                ).order_by(TextRecord.timestamp).all()
                
                return [self._text_to_dict(text) for text in texts]
                
        except SQLAlchemyError as e:
            storage_logger.error(f"获取文本记录失败: {e}")
            return []
    
    def get_segment_records(self, meeting_id: str) -> List[Dict[str, Any]]:
        """获取会议的段落记录"""
        try:
            with self.get_session() as session:
                segments = session.query(SegmentRecord).filter(
                    SegmentRecord.meeting_id == meeting_id
                ).order_by(SegmentRecord.start_time).all()
                
                return [self._segment_to_dict(segment) for segment in segments]
                
        except SQLAlchemyError as e:
            storage_logger.error(f"获取段落记录失败: {e}")
            return []
    
    def search_text_records(self, meeting_id: Optional[str] = None,
                           keyword: Optional[str] = None,
                           source_type: Optional[str] = None,
                           start_time: Optional[datetime] = None,
                           end_time: Optional[datetime] = None,
                           limit: int = 100) -> List[Dict[str, Any]]:
        """搜索文本记录"""
        try:
            with self.get_session() as session:
                query = session.query(TextRecord)
                
                # 添加过滤条件
                if meeting_id:
                    query = query.filter(TextRecord.meeting_id == meeting_id)
                
                if keyword:
                    query = query.filter(TextRecord.content.contains(keyword))
                
                if source_type:
                    query = query.filter(TextRecord.source_type == source_type)
                
                if start_time:
                    query = query.filter(TextRecord.timestamp >= start_time)
                
                if end_time:
                    query = query.filter(TextRecord.timestamp <= end_time)
                
                texts = query.order_by(TextRecord.timestamp.desc())\
                    .limit(limit).all()
                
                return [self._text_to_dict(text) for text in texts]
                
        except SQLAlchemyError as e:
            storage_logger.error(f"搜索文本记录失败: {e}")
            return []
    
    def update_meeting_status(self, meeting_id: str, status: str) -> bool:
        """更新会议状态"""
        try:
            with self.get_session() as session:
                meeting = session.query(MeetingRecord).filter(
                    MeetingRecord.meeting_id == meeting_id
                ).first()
                
                if meeting:
                    meeting.status = status
                    if status == "completed":
                        meeting.end_time = datetime.utcnow()
                    meeting.updated_at = datetime.utcnow()
                    
                    session.commit()
                    storage_logger.info(f"更新会议状态: {meeting_id} -> {status}")
                    return True
                
                return False
                
        except SQLAlchemyError as e:
            storage_logger.error(f"更新会议状态失败: {e}")
            return False
    
    def delete_meeting(self, meeting_id: str) -> bool:
        """删除会议及相关记录"""
        try:
            with self.get_session() as session:
                # 删除相关的文本和段落记录
                session.query(TextRecord).filter(
                    TextRecord.meeting_id == meeting_id
                ).delete()
                
                session.query(SegmentRecord).filter(
                    SegmentRecord.meeting_id == meeting_id
                ).delete()
                
                # 删除会议记录
                session.query(MeetingRecord).filter(
                    MeetingRecord.meeting_id == meeting_id
                ).delete()
                
                session.commit()
                storage_logger.info(f"删除会议记录: {meeting_id}")
                return True
                
        except SQLAlchemyError as e:
            storage_logger.error(f"删除会议记录失败: {e}")
            return False
    
    def _meeting_to_dict(self, meeting: MeetingRecord) -> Dict[str, Any]:
        """会议记录转字典"""
        return {
            "id": meeting.id,
            "meeting_id": meeting.meeting_id,
            "title": meeting.title,
            "description": meeting.description,
            "start_time": meeting.start_time.isoformat() if meeting.start_time else None,
            "end_time": meeting.end_time.isoformat() if meeting.end_time else None,
            "status": meeting.status,
            "metadata": meeting.metadata,
            "created_at": meeting.created_at.isoformat() if meeting.created_at else None,
            "updated_at": meeting.updated_at.isoformat() if meeting.updated_at else None
        }
    
    def _text_to_dict(self, text: TextRecord) -> Dict[str, Any]:
        """文本记录转字典"""
        return {
            "id": text.id,
            "text_id": text.text_id,
            "meeting_id": text.meeting_id,
            "content": text.content,
            "cleaned_content": text.cleaned_content,
            "source_type": text.source_type,
            "confidence": text.confidence,
            "timestamp": text.timestamp.isoformat() if text.timestamp else None,
            "language": text.language,
            "keywords": text.keywords,
            "region_ids": text.region_ids,
            "metadata": text.metadata,
            "created_at": text.created_at.isoformat() if text.created_at else None
        }
    
    def _segment_to_dict(self, segment: SegmentRecord) -> Dict[str, Any]:
        """段落记录转字典"""
        return {
            "id": segment.id,
            "segment_id": segment.segment_id,
            "meeting_id": segment.meeting_id,
            "content": segment.content,
            "source_type": segment.source_type,
            "start_time": segment.start_time.isoformat() if segment.start_time else None,
            "end_time": segment.end_time.isoformat() if segment.end_time else None,
            "confidence": segment.confidence,
            "keywords": segment.keywords,
            "related_segments": segment.related_segments,
            "metadata": segment.metadata,
            "created_at": segment.created_at.isoformat() if segment.created_at else None
        }
    
    def get_statistics(self) -> Dict[str, Any]:
        """获取数据库统计信息"""
        try:
            with self.get_session() as session:
                meeting_count = session.query(MeetingRecord).count()
                text_count = session.query(TextRecord).count()
                segment_count = session.query(SegmentRecord).count()
                
                return {
                    "meeting_count": meeting_count,
                    "text_count": text_count,
                    "segment_count": segment_count,
                    "database_url": self.database_url
                }
                
        except SQLAlchemyError as e:
            storage_logger.error(f"获取统计信息失败: {e}")
            return {}
    
    def cleanup(self):
        """清理数据库连接"""
        if self.engine:
            self.engine.dispose()
            storage_logger.info("数据库连接已清理")
